# coding=utf-8
"""
    @project: python-learning-notes
    @Author：frank
    @file： Run2.py
    @date：2023/9/13 20:35
"""
from operator import or_

from flask import Flask, request, render_template, redirect
# 将SQLAlchemy导入进来
from flask_sqlalchemy import SQLAlchemy

app = Flask(__name__)
# import pymysql
# pymysql.install_as_MySQLdb()
# app.config['SQLALCHEMY_DATABASE_URI']='mysql://username:pwd@host:port/dbname'
app.config['SQLALCHEMY_DATABASE_URI'] = 'mysql+pymysql://root:123456@localhost:3306/flask'
# 指定让SQLAlchemy自动追踪程序的修改
app.config['SQLALCHEMY_TRACK_MODIFICATIONS'] = True
# 指定执行完操作之后自动提交,不再需要db.session.commit()
app.config['SQLALCHEMY_COMMIT_ON_TEARDOWN'] = True

# 创建SQLAlchemy的实例
db = SQLAlchemy(app)


# db是SQLAlchemy的实例，表示程序正在使用的数据库，同时也获得了SQLAlchemy中的所有功能


# 创建模型类 Models
# 创建Users类，映射到表中叫users表
# 创建字段:id,主键，自增
# 创建字段:username,长度为80的字符串，不允许为空，必须唯一值
# 创建字段:age，整数，允许为空
# 创建字段:email,长度为120的字符串，必须唯一
class Users(db.Model):
    __tablename__ = 'users'
    id = db.Column(db.Integer, primary_key=True)
    username = db.Column(db.String(80), nullable=False, unique=True)
    age = db.Column(db.Integer)
    email = db.Column(db.String(120), unique=True)

    def __init__(self, username, age, email):
        self.username = username
        self.age = age
        self.email = email

    def __repr__(self):
        return '<Users:%r>' % self.username


class Student(db.Model):
    __tablename__ = 'student'
    id = db.Column(db.Integer, primary_key=True)
    sname = db.Column(db.String(30), nullable=False)
    sage = db.Column(db.Integer)

    def __init__(self, name, age):
        self.sname = age
        self.sage = age

    def __repr__(self):
        return '<Student:%s' % self.sname


class Teacher(db.Model):
    __tablename__ = 'teacher'
    id = db.Column(db.Integer, primary_key=True)
    tname = db.Column(db.String(30), nullable=False)
    tage = db.Column(db.Integer)

    def __init__(self, name, age):
        self.tname = name
        self.tage = age

    def __repr__(self):
        return '<Teacher:%s>' % self.tname


class Course(db.Model):
    __tablename__ = 'course'
    id = db.Column(db.Integer, primary_key=True)
    cname = db.Column(db.String(30))

    def __init__(self, name):
        self.cname = name

    def __repr__(self):
        return '<Course:%s>' % self.cname


# 将创建好的实体类映射到数据库
db.create_all()


@app.route('/insert')
def insert_views():
    # 创建User对象
    users = Users('王伟超', 38, 'wangwc@163.com')
    # 将对象通过db.session.add()插入到数据库
    db.session.add(users)
    # 提交插入操作
    db.session.commit()
    return 'Insert Success'


@app.route('/register', methods=['POST', 'GET'])
def register():
    if request.method == 'GET':
        return render_template('02-register.html')
    else:
        # 接受前端数据
        username = request.form.get('username')
        age = request.form.get('age')
        email = request.form.get('email')
        # 将数据构建出Users对象
        users = Users(username, age, email)
        # 通过db.session.add将对象保持进数据库
        db.session.add(users)
        # 提交
        db.session.commit()
        return "Register OK"


@app.route('/query')
def query_views():
    # print(db.session.query(Users))
    # 以下 查询结果笛卡尔积
    # print(db.session.query(Users, Course))
    # 返回所有结果对象
    # print(db.session.query(Users).all())
    # 查询第一条结果，如果数据为空则返回None
    # print(db.session.query(Users).first())
    # print(db.session.query(Course).first_or_404())
    # print(db.session.query(Users).filter(Users.age > 30).all())
    # print(db.session.query(Users).filter(Users.age > 30, Users.id > 5).all())
    print(db.session.query(Users).filter(or_(Users.age > 30, Users.id < 5)).all())
    return "Query OK"


@app.route('/query_all')
def query_all():
    # 查询Users表中所有的数据
    users = db.session.query(Users).all()
    print(users)
    # 将查询结果放到模板中进行显示
    return render_template('01-users.html', params=locals())


@app.route('/query_by_id/<int:id>')
def query_by_id(id):
    user = db.session.query(Users).filter_by(id=id).first()
    return render_template('02-user.html', params=locals())


@app.route('/delete_user/<int:id>')
def delete_user(id):
    user = Users.query.filter_by(id=id).first()
    db.session.delete(user)
    return redirect('/query_all')


@app.route('/add_course')
def add_course():
    course1 = Course('Python 基础')
    course2 = Course('Python 高级')
    course3 = Course('Python Web 基础')
    course4 = Course('Python Web 高级')

    db.session.add(course1)
    db.session.add(course2)
    db.session.add(course3)
    db.session.add(course4)

    return 'Course OK'


@app.route('/add_teacher')
def add_teacher():
    teacher = Teacher('魏老师', 35)
    # teacher.course_id = 1
    # 根据course_id查询出一个Course实体，再将Course实体赋值给teacher
    course = Course.query.filter_by(id=2)
    teacher.course = course
    db.session.add(teacher)

    return 'Add teacher OK'


@app.route('/register_teacher', methods=['POST', 'GET'])
def register_teacher():
    if request.method == 'GET':
        courses = Course.query.all()
        return render_template('04-register.html', params=locals())
    else:
        tname = request.form['tname']
        tage = request.form['tage']
        course_id = request.form['course_id']
        # 1 根据提交过来的course_id查询出对应的course对象
        course = Course.query.filter_by(id=course_id).first()
        # 2 创建teacher对象并将course对象赋值给teacher对象
        teacher = Teacher(tname, tage)
        teacher.course = course
        # 3 将teacher对象保存进数据库
        db.session.add(teacher)
        return 'Register OK'


if __name__ == "__main__":
    app.run(debug=True)
